/**
 * Copyright � 2012 GestureSoft. ALL RIGHTS RESERVED.
 * This file contains proprietary and GestureSoft CONFIDENTIAL Information.
 * Use, disclosure or reproduction is prohibited.
 * 
 * @fileName CardDBMediator.as
 * @creationDate Oct 19, 2012
 * @author GestureSoft
 * @version 1.0
 */
package com.gesturesoft.imultimax.model.cardmanager
{
	import com.gesturesoft.imultimax.model.DataBaseManager;
	import com.gesturesoft.imultimax.model.error.DataBaseError;
	import com.gesturesoft.imultimax.model.interfaces.IDatabaseManager;
	import com.gesturesoft.imultimax.model.vo.BankVO;
	import com.gesturesoft.imultimax.model.vo.CardDetailsVO;
	import com.gesturesoft.imultimax.model.vo.CategoryVO;
	import com.gesturesoft.imultimax.model.vo.ColorVO;
	
	import mx.collections.ArrayCollection;

	public class CardDBManager implements ICardDBManager
	{
		
		public function checkCardTemplateVersion(serverCardTemplateVersion:String):Boolean
		{
			
			var query:String;
			var iDataBaseManger:IDatabaseManager = new DataBaseManager();
			var localCardTemplateVersion:String;
			var versionChanged:Boolean = false;
			var arrTemplate:ArrayCollection;
			
			//var serverCardTemplateVersion:String = serverThemeVersion;
			
			
			query = "SELECT card_template_version FROM IM_CARD_TEMPLATES WHERE card_template_version='"+serverCardTemplateVersion+"'";
			
			arrTemplate = retrieveHandler(query);
			/*try
			{
				
				arrTemplate = iDataBaseManger.getData( query );
				
			} 
			catch(error:DataBaseError) 
			{
				
				throw error;
				trace("error  -> ThemeManger.checkThemeVersion()");
				
			}*/
			
			if(arrTemplate.length > 0)
			{
				
				if(serverCardTemplateVersion != arrTemplate[0].card_template_version )
				{
					//serverThemeVersion = serverThmeVertion;
					versionChanged = true;
				}
				
				
			}
			else
			{
				
				//serverThemeVersion = serverThmeVertion;
				versionChanged = true;
				
			}
			
			return versionChanged;
			
			
		}
		
		/**
		 * This is the function for inserting card details xml
		 * 
		 * @functionName process
		 * @creationDate Nov 12, 2012
		 * @author GestureSoft
		 * @params version:String
		 */
		public function process(version:String, cardDetailsXMLString:String):void
		{
			
			var delQuery:String = null;
			var insQuery:String = null;
			var iDataBaseManger:IDatabaseManager = new DataBaseManager();
			
			iDataBaseManger.setTransactionStartPoint();
			delQuery = "DELETE FROM IM_CARD_TEMPLATES";
			insQuery = "INSERT INTO IM_CARD_TEMPLATES (card_template_version, card_details_xml) VALUES('"+version+"','"+cardDetailsXMLString+"')";
			
			try
			{
				executeHandler(delQuery, iDataBaseManger);
				executeHandler(insQuery, iDataBaseManger);
			} 
			catch(error:Error) 
			{
				throw error;
			}
			
			iDataBaseManger.commitTransaction();
			
		}
		
		public function addCard(cardDetailVO:CardDetailsVO):CardDetailsVO
		{
			
			var dataBaseManger:IDatabaseManager = new DataBaseManager();
			var query:String;
			
			dataBaseManger.setTransactionStartPoint();
			
			try
			{
				
				if(cardDetailVO.cardIsFavourite)
				{
					//Set all the favourite field 'Not favourite'
					query =  "UPDATE IM_CARD_DETAILS SET card_is_favorite="+false+" WHERE card_type='"+cardDetailVO.cardType+"'";
					executeHandler( query, dataBaseManger );
					
				}
				else
				{
					
					//Set all the favourite field 'Not favourite'
					query =  "UPDATE IM_CARD_DETAILS SET card_is_sec_choice="+false+" WHERE card_type='"+cardDetailVO.cardType+"'";
					executeHandler( query, dataBaseManger );
					
				}
				
				
				query = "INSERT INTO IM_CARD_DETAILS ( card_type, card_number, card_pin, card_name, card_expiry_month, card_expiry_year, card_currency, card_balance, card_available_balance, card_color_id, card_skin_name, card_is_favorite, card_is_sec_choice ) " + 
					"VALUES('"+ 
					cardDetailVO.cardType +"','" + 
					cardDetailVO.cardNumber +"','" + 
					cardDetailVO.cardPin +"','" +
					cardDetailVO.cardName+"','" +
					cardDetailVO.cardExpiryMonth+"','" +
					cardDetailVO.cardExpiryYear+"','" +
					cardDetailVO.cardCurrency+"'," +
					cardDetailVO.cardBalance+"," +
					cardDetailVO.avilSpent+"," +
					cardDetailVO.colorID+",'" +
					cardDetailVO.skinName+"'," +
					cardDetailVO.cardIsFavourite+"," +
					cardDetailVO.cardIsSecondChoice+")";
				
				executeHandler( query, dataBaseManger );
				
				var cardID:int = int( retrieveHandler("SELECT last_insert_rowid() as ID",dataBaseManger)[0].ID );
				cardDetailVO.cardID = Number(cardID);
				
				dataBaseManger.commitTransaction();
				
			} 
			catch(error:Error) 
			{
				
				dataBaseManger.rollBackTransactions();
				throw error;
				
			}
			
			
			return cardDetailVO;
			
		}
		
		/**
		 * This is the function for update card details
		 * 
		 * @functionName updateCard
		 * @creationDate Oct 22, 2012
		 * @author GestureSoft
		 * @params cardDetailVO:CardDetailsVO
		 */
		public function updateCard(cardDetailVO:CardDetailsVO):void
		{
			
			var dataBaseManger:IDatabaseManager = new DataBaseManager();
			var query:String;
			
			dataBaseManger.setTransactionStartPoint();
			if(cardDetailVO.cardIsFavourite)
			{
				//Set all the favourite field 'Not favourite'
				query =  "UPDATE IM_CARD_DETAILS SET card_is_favorite="+false+" WHERE card_type='"+cardDetailVO.cardType+"'";
				executeHandler( query, dataBaseManger );
				
			}
			else
			{
				
				//Set all the favourite field 'Not favourite'
				query =  "UPDATE IM_CARD_DETAILS SET card_is_sec_choice="+false+" WHERE card_type='"+cardDetailVO.cardType+"'";
				executeHandler( query, dataBaseManger );
				
			}
			
			query = "UPDATE IM_CARD_DETAILS SET card_type='"+cardDetailVO.cardType+"'," +
				"card_number='"+cardDetailVO.cardNumber+"'," +
				"card_pin='"+cardDetailVO.cardPin+"'," +
				"card_name='"+cardDetailVO.cardName+"'," +
				"card_expiry_month='"+cardDetailVO.cardExpiryMonth+"'," +
				"card_expiry_year='"+cardDetailVO.cardExpiryYear+"'," +
				"card_currency='"+cardDetailVO.cardCurrency+"'," +
				"card_balance='"+cardDetailVO.cardBalance+"'," +
				"card_available_balance='"+cardDetailVO.avilSpent+"'," +
				"card_color_id='"+cardDetailVO.colorID+"'," +
				"card_skin_name='"+cardDetailVO.skinName+"'," +
				"card_is_favorite="+cardDetailVO.cardIsFavourite+"," +
				"card_is_sec_choice="+cardDetailVO.cardIsSecondChoice+" "+
				"WHERE card_id='"+cardDetailVO.cardID+"'";
			
			
			executeHandler( query, dataBaseManger );
			dataBaseManger.commitTransaction();
			
			
		}
		
		/**
		 * This is the function for updating the color id
		 * 
		 * @functionName updateCardColor
		 * @creationDate Oct 25, 2012
		 * @author GestureSoft
		 * @params colorID:Number
		 */
		public function updateCardStyle(cardDetailsVO:CardDetailsVO):void
		{
			
			var iDataBaseManager:IDatabaseManager = new DataBaseManager();
			var query:String =  "UPDATE IM_CARD_DETAILS SET card_color_id="+cardDetailsVO.colorID+", card_skin_name='"+cardDetailsVO.skinName+"' " +
				"WHERE card_id="+cardDetailsVO.cardID;
			executeHandler( query, iDataBaseManager );
			
		}
		
		/**
		 * This is the function for delete a card
		 * 
		 * @functionName deleteCard
		 * @creationDate Oct 22, 2012
		 * @author GestureSoft
		 * @params cardDetailVO:CardDetailsVO
		 */
		public function deleteCard(cardID:Number):void
		{
			
			var dataBaseManger:IDatabaseManager = new DataBaseManager();
			
			var query:String = "DELETE FROM IM_CARD_DETAILS WHERE card_id='"+cardID+"'";
			executeHandler( query, dataBaseManger );
			
		}
		
		
		/**
		 * This is the function for getting card details
		 * 
		 * @functionName getCardDetails
		 * @creationDate Oct 22, 2012
		 * @return ArrayCollection
		 * @author GestureSoft
		 */
		public function getCardDetails(type:String):ArrayCollection
		{
			
			var query:String = "SELECT IM_CARD_DETAILS.*,IM_CARD_COLORS.card_color_gradient1,IM_CARD_COLORS.card_color_gradient2 FROM IM_CARD_DETAILS, IM_CARD_COLORS WHERE IM_CARD_DETAILS.card_color_id=IM_CARD_COLORS.card_color_id AND IM_CARD_DETAILS.card_type='"+type+"'";
			var arrCol:ArrayCollection = retrieveHandler(query);
			
			var resultArrCol:ArrayCollection = new ArrayCollection();
			for each(var item:Object in arrCol)
			{
				
				var cardDetailsVO:CardDetailsVO = new CardDetailsVO();
				
				cardDetailsVO.cardID = item.card_id;
				cardDetailsVO.cardExpiryMonth = item.card_expiry_month;
				cardDetailsVO.cardExpiryYear = item.card_expiry_year;
				cardDetailsVO.cardIsFavourite = (item.card_is_favorite == 0)?false:true;
				cardDetailsVO.cardIsSecondChoice = (item.card_is_sec_choice == 0)?false:true;
				cardDetailsVO.cardPin = item.card_pin;
				cardDetailsVO.cardType = item.card_type;
				cardDetailsVO.cardNumber = item.card_number;
				cardDetailsVO.cardName = item.card_name;
				cardDetailsVO.gradientOne = item.card_color_gradient1;
				cardDetailsVO.gradientTwo = item.card_color_gradient2;
				cardDetailsVO.skinName = item.card_skin_name;
				cardDetailsVO.cardName = item.card_name;
				cardDetailsVO.cardBalance = item.card_balance;
				cardDetailsVO.avilSpent = item.card_available_balance;
				cardDetailsVO.cardCurrency = item.card_currency;
				cardDetailsVO.colorID = item.card_color_id;
				
				resultArrCol.addItem(cardDetailsVO);
				
			}
			
			return resultArrCol;
			
		}
		
		public function setFavourite(cardID:Number):void
		{
			
			var dataBaseManger:IDatabaseManager = new DataBaseManager();
			
			dataBaseManger.setTransactionStartPoint();
			//Set all the favourite field 'Not favourite'
			var query:String =  "UPDATE IM_CARD_DETAILS SET card_is_favorite="+false+"";
			executeHandler( query, dataBaseManger );
				
			//Set the selected card as favourite
			query =  "UPDATE IM_CARD_DETAILS SET card_is_favorite="+true+" WHERE card_id='"+cardID+"'";
			executeHandler( query, dataBaseManger );
			
			dataBaseManger.commitTransaction();	
			
		}
		
		public function setSecondChoice(cardID:Number):void
		{
			
			var dataBaseManger:IDatabaseManager = new DataBaseManager();;
			
			dataBaseManger.setTransactionStartPoint();
			//Set all the favourite field 'Not favourite'
			var query:String =  "UPDATE IM_CARD_DETAILS SET card_is_sec_choice='"+false+"'";
			executeHandler( query, dataBaseManger );
			
			//Set the selected card as favourite
			query =  "UPDATE IM_CARD_DETAILS SET card_is_sec_choice='"+true+"' WHERE card_id='"+cardID+"'";
			executeHandler( query, dataBaseManger );
			
			dataBaseManger.commitTransaction();	
			
		}
		
		public function getColors():ArrayCollection
		{
			
			var dataBaseManger:IDatabaseManager = new DataBaseManager();
			var query:String =  "SELECT * FROM IM_CARD_COLORS";
			var arrCol:ArrayCollection = retrieveHandler(query);
			var resArrayCol:ArrayCollection = new ArrayCollection();
			
			for each(var color:Object in arrCol)
			{
				
				var colorVO:ColorVO = new ColorVO();
				
				colorVO.color_id = color.card_color_id;
				colorVO.gradient1 = color.card_color_gradient1;
				colorVO.gradient2 = color.card_color_gradient2;
				resArrayCol.addItem(colorVO);
				
			}
			
			return resArrayCol;
			
		}
		
		//-----------------------------------------START:Bank table operations--------------------------------------------------//
		
		/**
		 * This is the function for getting bank details
		 * 
		 * @functionName getBandDetails
		 * @creationDate Oct 22, 2012
		 * @return ArrayCollection
		 * @author GestureSoft
		 */
		public function getBankDetails():ArrayCollection
		{
			
			var resultArrayCol:ArrayCollection;
			var query:String = "SELECT * FROM IM_BANKS";
			var arrCol:ArrayCollection = retrieveHandler(query);
			
			resultArrayCol = new ArrayCollection();
			for each(var item:Object in arrCol)
			{
				
				var bankDetails:BankVO = new BankVO();
				bankDetails.bankName = item.bank_name;
				bankDetails.bankLogo = item.bank_logo;
				bankDetails.bankID = item.bank_id;
				
				resultArrayCol.addItem(bankDetails);
				
			}
			
			return resultArrayCol;
			
		}
		//-----------------------------------------END:Bank table operations--------------------------------------------------//
		
		//-----------------------------------------Start Functions for Wallet Category--------------------------------------------------//
		
		public function addWalletCategory(categoryVO:CategoryVO):void
		{
			
			var dataBaseManger:IDatabaseManager = new DataBaseManager();
			
			var query:String = "INSERT INTO IM_WALLET_CATEGORY( category_id, category_name, view, view_Class ) " + 
				"VALUES('"+ 
				categoryVO.categoryid +"','" + 
				categoryVO.categoryname +"','" + 
				categoryVO.view +"','" + 
				categoryVO.viewClass +"')";
			
			executeHandler( query, dataBaseManger );
			
		}
		
		public function getWalletCategoryList():XML
		{
			
			var resultArrayCol:ArrayCollection;
			var query:String = "SELECT card_details_xml FROM IM_CARD_TEMPLATES";
			var arrCol:ArrayCollection = retrieveHandler(query);
			
			resultArrayCol = new ArrayCollection();
			
			/*for each(var item:Object in arrCol)
			{
				
				var categoryVO:CategoryVO = new CategoryVO();
				categoryVO.categoryid = item.category_id;
				categoryVO.categoryname = item.category_name;
				categoryVO.view = item.view;
				categoryVO.viewClass = item.view_Class;
				
				resultArrayCol.addItem(categoryVO);
				
			}*/
			
			return XML(arrCol[0].card_details_xml);
			
		}
		
		
		//-----------------------------------------End Functions for Wallet Category--------------------------------------------------//
		
		private function executeHandler(val:String, iDataBaseManger:IDatabaseManager):void
		{
			
			try
			{
				
				iDataBaseManger.execute(val);
				
			} 
			catch(error:DataBaseError) 
			{
				
				iDataBaseManger.rollBackTransactions();
				trace("ApplicationStyleUpdator-executeHandler() : Database execution error");
				throw error;
				
			}
			
		}
		
			
		/**
		 * This is the function for retrieving datas from database as collection of arrays
		 * 
		 * @functionName retreiveHandler
		 * @creationDate Sep 5, 2012
		 * @author GestureSoft
		 * @params query:String
		 */
		private function retrieveHandler(val:String, iDataBaseManager:IDatabaseManager=null):ArrayCollection
		{
			
				
			var _iDataBaseManager:IDatabaseManager;
			if(iDataBaseManager)
				_iDataBaseManager = iDataBaseManager;
			else
				_iDataBaseManager = new DataBaseManager;
			var result:ArrayCollection;
			
			try
			{
				
				result =  _iDataBaseManager.getData(val);
				
			} 
			catch(error:DataBaseError) 
			{
				
				trace("ApplicationStyleUpdator-retrieveHandler() : Database Read error");
				throw error;
				
			}
			
			return result;
			
		}
		
	}
}